package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import configuration.Configuration;
import pojo.Categories;
import pojo.Food;
import util.DBUtils;
import dao.FoodsDao;

public class FoodsDaoImpl extends BaseDaoImpl<Food> implements FoodsDao{

	@Override
	public int add(Food food) {
		String sql = "insert into foods(id,name,price,image,c_id,m_id) values(?,?,?,?,?,?)";
		Connection con=null;
		PreparedStatement pst = null;
		con = DBUtils.createConnection();
		int m = 0;
		try {
			con=DBUtils.createConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, food.getId());
			pst.setString(2, food.getName());
			pst.setDouble(3, food.getPrice());
			pst.setString(4, food.getImage());
			pst.setString(5, food.getcId());
			pst.setString(6, food.getmId());
			m = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		finally {
			DBUtils.close(con, pst, null);
		}
		return m;
		
	}

	@Override 
	public int delete(String id) {
		String sql = "delete foods where id=?";
		Connection con=null;
		PreparedStatement pst = null;
		int m = 0;
		try {
			con=DBUtils.createConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, id);
			m = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(con, pst, null);
		}
		return m;
	}

	@Override
	public int update(Food food) {
		String sql = "update foods set name=?,price=?,c_id=?,m_id=? where id=?";
		Connection con=null;
		PreparedStatement pst = null;
		int m = 0;
		try {
			con=DBUtils.createConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, food.getName());
			pst.setDouble(2, food.getPrice());
			pst.setString(3,food.getcId());
			pst.setString(4, food.getId());
			pst.setString(5, food.getmId());
			m = pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(con, pst, null);
		}
		return m;
	}

	@Override
	public Food load(String id) {
		String sql = "select * from foods where id =?";
		Connection con =null;
		PreparedStatement pre = null;
		ResultSet rs = null;
		Food food =null;
		try {
			con = DBUtils.createConnection();
			pre = con.prepareStatement(sql);
			pre.setString(1, id);
			rs = pre.executeQuery();
			while(rs.next()){
				food = new Food();
				food.setId(id);
				food.setName(rs.getString("name"));
				food.setPrice(rs.getDouble("price"));
				food.setImage(rs.getString("image"));
				food.setcId(rs.getString("c_id"));
				food.setmId(rs.getString("m_id"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtils.close(con, pre, rs);
		}
		return food;
	}

	@Override
	public List<Food> findAll() {
		List<Food> list=new ArrayList<Food>();
		String sql = "select * from foods";
		Connection con=null;
		PreparedStatement pst = null;
		ResultSet rs =null;
		try {
			con=DBUtils.createConnection();
			pst = con.prepareStatement(sql);
			rs = pst.executeQuery();
			Food food = null;
			while (rs.next()) {
				String id = rs.getString("id");
				String name = rs.getString("name");
				double price = rs.getDouble("price");	
				String image = rs.getString("image");
				String cId = rs.getString("c_id");
				String mId = rs.getString("m_id");
				food = new Food(id, name, price, image, cId, mId); 
				list.add(food);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			DBUtils.close(con, pst, rs);
		}
		return list;
	}
	
	@Override
	public List<Food> find(String m_id, String type) {
		List<Food> list = null;
		String sql = null;
		if("all".equals(type))
			sql = "select * from foods where m_id=?";
		else
			sql = "select * from foods where m_id=? and c_id=?";
		
		Connection con=null;
		PreparedStatement pst = null;
		ResultSet rs =null;
		try {
			con=DBUtils.createConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, m_id);
			if(!"all".equals(type))
				pst.setString(2, type);
			rs = pst.executeQuery();
			Food food = null;
			list=new ArrayList<Food>();
			while (rs.next()) {
				String id = rs.getString("id");
				String name = rs.getString("name");
				double price = rs.getDouble("price");
				String image = rs.getString("image");
				String cId = rs.getString("c_id");
				String mId = rs.getString("m_id");
				food = new Food(id, name, price, image, cId, mId); 
				list.add(food);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			DBUtils.close(con, pst, rs);
		}
		return list;
	}
	public List<Food> find(int current, int pageSize, String m_id, String type) {
		int begin = (current - 1) * pageSize + 1;
	    int end = begin + pageSize - 1;
		List<Food> list = new ArrayList<Food>();
		String sql = null;
		if("all".equals(type))
			sql = "select * from foods where m_id=?";
		else
			sql = "select * from foods where m_id=? and c_id=?";
		
		String sql1 = "select * from(select rownum as rn, f.* from ("+ sql +") f) temp where temp.rn between ? and ?";
		Connection con=null;
		PreparedStatement pst = null;
		ResultSet rs =null;
		try {
			con=DBUtils.createConnection();
			pst = con.prepareStatement(sql1);
			if(!"all".equals(type)){
				pst.setString(1, m_id);
				pst.setString(2, type);
				pst.setInt(3, begin);
				pst.setInt(4, end);
			}
			else{
				pst.setString(1, m_id);
				pst.setInt(2, begin);
				pst.setInt(3, end);
			}
			rs = pst.executeQuery();
			Food food = null;
			while (rs.next()) {
				String id = rs.getString("id");
				String name = rs.getString("name");
				double price = rs.getDouble("price");
				String image = rs.getString("image");
				String cId = rs.getString("c_id");
				String mId = rs.getString("m_id");
				food = new Food(id, name, price, image, cId, mId); 
				list.add(food);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			DBUtils.close(con, pst, rs);
		}
		return list;
	}
	
	public int getCount(String m_id, String type){
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		String sql = null;
		if("all".equals(type))
			sql = "select * from foods where m_id=?";
		else
			sql = "select * from foods where m_id=? and c_id=?";
		String sql1 = "select count(0) as c from ("+ sql +")";
		int count = 0;
		try {
	        con = DBUtils.createConnection();
	        pst = con.prepareStatement(sql1);
	        pst.setString(1, m_id);
			if(!"all".equals(type)){
				pst.setString(2, type);
			}
			rs = pst.executeQuery();
			if(rs.next()) {
			    count = rs.getInt("c");
		    }
		} catch (SQLException e) {
		    e.printStackTrace();
		} finally {
		    DBUtils.close(con, pst, rs);
		}
		return count;
	}
	@Override
	public List<Food> findByCid(String cId){
		List<Food> list=new ArrayList<Food>();
		String sql = "select * from foods where c_id =?";
		Connection con=null;
		PreparedStatement pst = null;
		ResultSet rs =null;
		try {
			con=DBUtils.createConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, cId);
			rs = pst.executeQuery();
			Food food = null;
			while (rs.next()) {
				String id = rs.getString("id");
				String name = rs.getString("name");
				double price = rs.getDouble("price");	
				String image = rs.getString("image");
				String mId = rs.getString("m_id");
				food = new Food(id, name, price, image, cId, mId); 
				list.add(food);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			DBUtils.close(con, pst, rs);
		}
		return list;
	}
}
